Creating datasets
Prerequisites
- Access to the Configuration > Dataset type (FO011) menu.
- Access to the File > Dataset (FO009) menu.
- Data source configured in SoftExpert Administration.
Introduction
A dataset is a collection of data built from an SQL command, with data coming from external data sources and databases.
It is associated with forms through lists of values that display data external to the system. Thus, dataset SQLs must be configured to show the information required for the forms.
See how to configure datasets:
Creating a dataset type
Before creating the datasets, we need to configure the types used to classify them.
Such types make it easier to organize the records in the system and allow for creating a hierarchy among them.
To create a dataset type, take the following steps:
1. Access the Configuration > Dataset type (FO011) menu.
2. Click on the 
button.
3. Add an ID # and a Name for the dataset.
4. If you wish for the type to be added hierarchically below another dataset type, fill in the Upper level type field.
5. Click on the 
button.
Creating a dataset
Additional information
• In the Construct query step, enter the SQL command that will define the search criteria and the data that will be used in the dataset that is being configured.
• The Data source establishes the connection to the database and must be configured beforehand in SoftExpert Administration. If the user has permission, the 
button will be shown next to the field, allowing them to add a new data source.
• After the SQL command is inserted in the Construct query field, some buttons will be made available for use:
| Execute (Ctrl + Enter) | Click on this button to test the query inserted in the Construct query field. If any command errors occur, they will be shown on the screen. |
| Edit parameters | This button will be enabled only when there is a condition in the query whose value is variable. For this reason, use ":" after the statement to identify that the value must be a parameter. For example: SELECT IDLOGIN, NMUSER FROM ADUSER WHERE IDLOGIN =:ParamLogin Where 'ParamLogin' is a parameter of the form. If there are parameters in the query, when the Execute button is clicked, the system will automatically display the parameter screen, in which the values for the query execution simulation must be entered. |
| Sort data | When this button is clicked, a screen for setting the sorting parameters will be displayed. In the Field section, select the column (of the query) by which the data will be sorted, and, in the Orientation section, define whether the sorting will be ascending or descending. If you need to sort the data using a second column, click on Add new and insert the column and the sorting orientation. Then, click on Apply so that the sorting is applied to the query result. Note: Sorting the data will only be possible through the columns returned in the query (defined in the SELECT statement). If the SQL command is showing all columns (SELECT * FROM TABLE), all columns will be shown in the Field section. |
| Format | Click on this button for the system to apply a format (indentation) to the SQL command, making it more legible for future changes. |
• To add a user, team, department, or position to the Security list, simply type the desired name in the available field and select the corresponding option from the list that will be displayed, or press "Enter" on the keyboard. To delete, click on the 
button located next to the desired record.
• Use the 
button, located on the toolbar of the dataset file menu, to simulate the dataset. When this button is clicked, a screen containing the values of the dataset parameters will open.
Conclusion
With the dataset configured, it is possible to configure a form field to fetch external data via dataset.